using System;
using System.Collections.Generic;
using System.Linq;
using Model;
using DBUtility;
using MySql.Data.MySqlClient;
using System.Text;

namespace DAL
{
    public class MaterialRepository
    {
        private static string connectionString = "server=localhost;database=mess;userid=root;password=1488679583;";

        // 添加材料信息
        public int AddMaterial(Material material)
        {
            string sql = @"INSERT INTO material 
                            (MaterialType, DetailedMaterialType, Quantity, MaterialUnit, MaterialDescription, ConsumptionDeduction, MaterialGroupNumber) 
                           VALUES 
                            (@MaterialType, @DetailedMaterialType, @Quantity, @MaterialUnit, @MaterialDescription, @ConsumptionDeduction, @MaterialGroupNumber)";

            MySqlParameter[] parameters = {
                new MySqlParameter("@MaterialType", material.MaterialType),
                new MySqlParameter("@DetailedMaterialType", material.DetailedMaterialType),
                new MySqlParameter("@Quantity", material.Quantity),
                new MySqlParameter("@MaterialUnit", material.MaterialUnit),
                new MySqlParameter("@MaterialDescription", material.MaterialDescription),
                new MySqlParameter("@ConsumptionDeduction", material.ConsumptionDeduction),
                new MySqlParameter("@MaterialGroupNumber", material.MaterialGroupNumber)
            };

            return DBHelper.ExecuteNonQuery(sql, parameters);
        }

        // 删除材料信息
        // 修改删除方法，添加历史记录
        public int DeleteMaterial(int materialId)
        {
            Material materialToDelete = GetMaterialById(materialId);
            if (materialToDelete == null) return 0;

            using (var conn = new MySqlConnection(connectionString))
            {
                conn.Open();
                using (var transaction = conn.BeginTransaction())
                {
                    try
                    {
                        // 删除操作
                        string deleteSql = "DELETE FROM material WHERE MaterialId = @MaterialId";
                        MySqlParameter[] deleteParams = { new MySqlParameter("@MaterialId", materialId) };
                        int rowsAffected = ExecuteNonQueryInTransaction(deleteSql, deleteParams, conn, transaction);

                        if (rowsAffected > 0)
                        {
                            // 创建历史记录（修复：使用正确的字段名）
                            var history = new Model.Material.History
                            {
                                MaterialId = materialId,
                                ChangeType = "DELETE",
                                ChangeTime = DateTime.Now,
                                // 修复：使用正确的字段名（PreviousMaterialType -> OldMaterialType）
                                OldMaterialType = materialToDelete.MaterialType,
                                OldDetailedMaterialType = materialToDelete.DetailedMaterialType,
                                OldQuantity = materialToDelete.Quantity,
                                OldMaterialUnit = materialToDelete.MaterialUnit,
                                OldMaterialDescription = materialToDelete.MaterialDescription,
                                OldConsumptionDeduction = materialToDelete.ConsumptionDeduction,
                                OldMaterialGroupNumber = materialToDelete.MaterialGroupNumber
                            };

                            // 插入历史记录
                            InsertMaterialHistory(history, conn, transaction);
                        }
                        transaction.Commit();
                        return rowsAffected;
                    }
                    catch
                    {
                        transaction.Rollback();
                        throw;
                    }
                }
            }
        }
        private int InsertMaterialHistory(Model.Material.History history, MySqlConnection conn, MySqlTransaction transaction)
        {
            string sql = @"INSERT INTO materialHistory 
        (MaterialId, ChangeType, ChangeTime, 
         OldMaterialType, NewMaterialType, 
         OldDetailedMaterialType, NewDetailedMaterialType,
         OldQuantity, NewQuantity,
         OldMaterialUnit, NewMaterialUnit,
         OldMaterialDescription, NewMaterialDescription,
         OldConsumptionDeduction, NewConsumptionDeduction,
         OldMaterialGroupNumber, NewMaterialGroupNumber) 
       VALUES 
        (@MaterialId, @ChangeType, @ChangeTime,
         @OldMaterialType, @NewMaterialType, 
         @OldDetailedMaterialType, @NewDetailedMaterialType,
         @OldQuantity, @NewQuantity,
         @OldMaterialUnit, @NewMaterialUnit,
         @OldMaterialDescription, @NewMaterialDescription,
         @OldConsumptionDeduction, @NewConsumptionDeduction,
         @OldMaterialGroupNumber, @NewMaterialGroupNumber)";

            MySqlParameter[] parameters = {
        new MySqlParameter("@MaterialId", history.MaterialId),
        new MySqlParameter("@ChangeType", history.ChangeType),
        new MySqlParameter("@ChangeTime", history.ChangeTime),
        new MySqlParameter("@OldMaterialType", history.OldMaterialType ?? (object)DBNull.Value),
        new MySqlParameter("@NewMaterialType", history.NewMaterialType ?? (object)DBNull.Value),
        new MySqlParameter("@OldDetailedMaterialType", history.OldDetailedMaterialType ?? (object)DBNull.Value),
        new MySqlParameter("@NewDetailedMaterialType", history.NewDetailedMaterialType ?? (object)DBNull.Value),
        new MySqlParameter("@OldQuantity", history.OldQuantity ?? (object)DBNull.Value),
        new MySqlParameter("@NewQuantity", history.NewQuantity ?? (object)DBNull.Value),
        new MySqlParameter("@OldMaterialUnit", history.OldMaterialUnit ?? (object)DBNull.Value),
        new MySqlParameter("@NewMaterialUnit", history.NewMaterialUnit ?? (object)DBNull.Value),
        new MySqlParameter("@OldMaterialDescription", history.OldMaterialDescription ?? (object)DBNull.Value),
        new MySqlParameter("@NewMaterialDescription", history.NewMaterialDescription ?? (object)DBNull.Value),
        new MySqlParameter("@OldConsumptionDeduction", history.OldConsumptionDeduction ?? (object)DBNull.Value),
        new MySqlParameter("@NewConsumptionDeduction", history.NewConsumptionDeduction ?? (object)DBNull.Value),
        new MySqlParameter("@OldMaterialGroupNumber", history.OldMaterialGroupNumber ?? (object)DBNull.Value),
        new MySqlParameter("@NewMaterialGroupNumber", history.NewMaterialGroupNumber ?? (object)DBNull.Value)
    };

            return ExecuteNonQueryInTransaction(sql, parameters, conn, transaction);
        }

        // 修改更新方法，添加历史记录
        public int UpdateMaterial(Material material)
        {
            Material originalMaterial = GetMaterialById(material.MaterialId);
            if (originalMaterial == null) return 0;

            using (var conn = new MySqlConnection(connectionString))
            {
                conn.Open();
                using (var transaction = conn.BeginTransaction())
                {
                    try
                    {
                        // 更新操作
                        string updateSql = @"UPDATE material 
                    SET MaterialType = @MaterialType, 
                        DetailedMaterialType = @DetailedMaterialType, 
                        Quantity = @Quantity, 
                        MaterialUnit = @MaterialUnit, 
                        MaterialDescription = @MaterialDescription, 
                        ConsumptionDeduction = @ConsumptionDeduction, 
                        MaterialGroupNumber = @MaterialGroupNumber 
                    WHERE MaterialId = @MaterialId";

                        MySqlParameter[] updateParams = {
                    new MySqlParameter("@MaterialType", material.MaterialType),
                    new MySqlParameter("@DetailedMaterialType", material.DetailedMaterialType),
                    new MySqlParameter("@Quantity", material.Quantity),
                    new MySqlParameter("@MaterialUnit", material.MaterialUnit),
                    new MySqlParameter("@MaterialDescription", material.MaterialDescription),
                    new MySqlParameter("@ConsumptionDeduction", material.ConsumptionDeduction),
                    new MySqlParameter("@MaterialGroupNumber", material.MaterialGroupNumber),
                    new MySqlParameter("@MaterialId", material.MaterialId)
                };

                        int rowsAffected = ExecuteNonQueryInTransaction(updateSql, updateParams, conn, transaction);

                        if (rowsAffected > 0)
                        {
                            // 创建历史记录（修复：使用正确的字段名）
                            var history = new Model.Material.History
                            {
                                MaterialId = material.MaterialId,
                                ChangeType = "UPDATE",
                                ChangeTime = DateTime.Now,
                                // 修复：使用正确的字段名（Previous->Old, New->New）
                                OldMaterialType = originalMaterial.MaterialType,
                                NewMaterialType = material.MaterialType,
                                OldDetailedMaterialType = originalMaterial.DetailedMaterialType,
                                NewDetailedMaterialType = material.DetailedMaterialType,
                                OldQuantity = originalMaterial.Quantity,
                                NewQuantity = material.Quantity,
                                OldMaterialUnit = originalMaterial.MaterialUnit,
                                NewMaterialUnit = material.MaterialUnit,
                                OldMaterialDescription = originalMaterial.MaterialDescription,
                                NewMaterialDescription = material.MaterialDescription,
                                OldConsumptionDeduction = originalMaterial.ConsumptionDeduction,
                                NewConsumptionDeduction = material.ConsumptionDeduction,
                                OldMaterialGroupNumber = originalMaterial.MaterialGroupNumber,
                                NewMaterialGroupNumber = material.MaterialGroupNumber
                            };

                            // 插入历史记录
                            InsertMaterialHistory(history, conn, transaction);
                        }
                        transaction.Commit();
                        return rowsAffected;
                    }
                    catch
                    {
                        transaction.Rollback();
                        throw;
                    }
                }
            }
        }

        // 查询所有材料信息
        public List<Material> GetAllMaterials()
        {
            string sql = "SELECT * FROM material";
            return DBHelper.ExecuteQuery(sql, null, MapMaterial);
        }

        // 根据ID查询单个材料信息
        public Material GetMaterialById(int materialId)
        {
            string sql = "SELECT * FROM material WHERE MaterialId = @MaterialId";
            MySqlParameter[] parameters = { new MySqlParameter("@MaterialId", materialId) };
            var materials = DBHelper.ExecuteQuery(sql, parameters, MapMaterial);
            return materials.FirstOrDefault();
        }

        // 获取去重的物料类型
        public List<string> GetDistinctMaterialTypes()
        {
            string sql = "SELECT DISTINCT MaterialType FROM material WHERE MaterialType IS NOT NULL AND MaterialType <> ''";
            List<string> materialTypes = new List<string>();

            using (MySqlConnection conn = new MySqlConnection(connectionString))
            {
                conn.Open();
                using (MySqlCommand cmd = new MySqlCommand(sql, conn))
                {
                    using (MySqlDataReader reader = cmd.ExecuteReader())
                    {
                        while (reader.Read())
                        {
                            materialTypes.Add(reader.GetString("MaterialType"));
                        }
                    }
                }
            }

            return materialTypes;
        }

        // 根据物料类型获取去重的详细类型
        public List<string> GetDistinctDetailedTypes(string materialType)
        {
            string sql = "SELECT DISTINCT DetailedMaterialType FROM material WHERE MaterialType = @MaterialType AND DetailedMaterialType IS NOT NULL AND DetailedMaterialType <> ''";
            List<string> detailedTypes = new List<string>();
            MySqlParameter[] parameters = { new MySqlParameter("@MaterialType", materialType) };

            using (MySqlConnection conn = new MySqlConnection(connectionString))
            {
                conn.Open();
                using (MySqlCommand cmd = new MySqlCommand(sql, conn))
                {
                    cmd.Parameters.AddRange(parameters);
                    using (MySqlDataReader reader = cmd.ExecuteReader())
                    {
                        while (reader.Read())
                        {
                            detailedTypes.Add(reader.GetString("DetailedMaterialType"));
                        }
                    }
                }
            }

            return detailedTypes;
        }

        // 根据条件筛选材料
        public List<Material> SearchMaterials(string materialType, string detailedType, int? materialId)
        {
            StringBuilder sql = new StringBuilder("SELECT * FROM material WHERE 1=1");
            List<MySqlParameter> parameters = new List<MySqlParameter>();

            if (!string.IsNullOrWhiteSpace(materialType))
            {
                sql.Append(" AND MaterialType = @MaterialType");
                parameters.Add(new MySqlParameter("@MaterialType", materialType));
            }

            if (!string.IsNullOrWhiteSpace(detailedType))
            {
                sql.Append(" AND DetailedMaterialType = @DetailedType");
                parameters.Add(new MySqlParameter("@DetailedType", detailedType));
            }

            if (materialId.HasValue)
            {
                sql.Append(" AND MaterialId = @MaterialId");
                parameters.Add(new MySqlParameter("@MaterialId", materialId.Value));
            }

            return DBHelper.ExecuteQuery(sql.ToString(), parameters.ToArray(), MapMaterial);
        }

        // 映射数据库查询结果到 Material 对象
        private Material MapMaterial(MySqlDataReader reader)
        {
            return new Material
            {
                MaterialId = (int)reader["MaterialId"],
                MaterialType = reader["MaterialType"] is DBNull ? null : reader["MaterialType"].ToString(),
                DetailedMaterialType = reader["DetailedMaterialType"] is DBNull ? null : reader["DetailedMaterialType"].ToString(),
                Quantity = reader["Quantity"] is DBNull ? (int?)null : (int)reader["Quantity"],
                MaterialUnit = reader["MaterialUnit"] is DBNull ? null : reader["MaterialUnit"].ToString(),
                MaterialDescription = reader["MaterialDescription"] is DBNull ? null : reader["MaterialDescription"].ToString(),
                ConsumptionDeduction = reader["ConsumptionDeduction"] is DBNull ? null : reader["ConsumptionDeduction"].ToString(),
                MaterialGroupNumber = reader["MaterialGroupNumber"] is DBNull ? null : reader["MaterialGroupNumber"].ToString()
            };
        }
        public int InsertMaterialHistory(int materialId, Material previousMaterial, Material newMaterial)
        {
            string sql = @"INSERT INTO material_history 
                    (MaterialId, PreviousMaterialType, NewMaterialType, PreviousDetailedMaterialType, NewDetailedMaterialType, 
                     PreviousQuantity, NewQuantity, PreviousMaterialUnit, NewMaterialUnit, 
                     PreviousMaterialDescription, NewMaterialDescription, PreviousConsumptionDeduction, NewConsumptionDeduction, 
                     PreviousMaterialGroupNumber, NewMaterialGroupNumber) 
                   VALUES 
                    (@MaterialId, @PreviousMaterialType, @NewMaterialType, @PreviousDetailedMaterialType, @NewDetailedMaterialType, 
                     @PreviousQuantity, @NewQuantity, @PreviousMaterialUnit, @NewMaterialUnit, 
                     @PreviousMaterialDescription, @NewMaterialDescription, @PreviousConsumptionDeduction, @NewConsumptionDeduction, 
                     @PreviousMaterialGroupNumber, @NewMaterialGroupNumber)";

            MySqlParameter[] parameters = {
        new MySqlParameter("@MaterialId", materialId),
        new MySqlParameter("@PreviousMaterialType", previousMaterial.MaterialType),
        new MySqlParameter("@NewMaterialType", newMaterial.MaterialType),
        new MySqlParameter("@PreviousDetailedMaterialType", previousMaterial.DetailedMaterialType),
        new MySqlParameter("@NewDetailedMaterialType", newMaterial.DetailedMaterialType),
        new MySqlParameter("@PreviousQuantity", previousMaterial.Quantity),
        new MySqlParameter("@NewQuantity", newMaterial.Quantity),
        new MySqlParameter("@PreviousMaterialUnit", previousMaterial.MaterialUnit),
        new MySqlParameter("@NewMaterialUnit", newMaterial.MaterialUnit),
        new MySqlParameter("@PreviousMaterialDescription", previousMaterial.MaterialDescription),
        new MySqlParameter("@NewMaterialDescription", newMaterial.MaterialDescription),
        new MySqlParameter("@PreviousConsumptionDeduction", previousMaterial.ConsumptionDeduction),
        new MySqlParameter("@NewConsumptionDeduction", newMaterial.ConsumptionDeduction),
        new MySqlParameter("@PreviousMaterialGroupNumber", previousMaterial.MaterialGroupNumber),
        new MySqlParameter("@NewMaterialGroupNumber", newMaterial.MaterialGroupNumber)
    };

            return DBHelper.ExecuteNonQuery(sql, parameters);
        }
        public int InsertMaterialHistory(Model.Material.History history)
        {
            string sql = @"INSERT INTO materialHistory 
            (MaterialId, ChangeType, ChangeTime, 
             OldMaterialType, NewMaterialType, 
             OldDetailedMaterialType, NewDetailedMaterialType,
             OldQuantity, NewQuantity,
             OldMaterialUnit, NewMaterialUnit,
             OldMaterialDescription, NewMaterialDescription,
             OldConsumptionDeduction, NewConsumptionDeduction,
             OldMaterialGroupNumber, NewMaterialGroupNumber) 
           VALUES 
            (@MaterialId, @ChangeType, @ChangeTime,
             @OldMaterialType, @NewMaterialType, 
             @OldDetailedMaterialType, @NewDetailedMaterialType,
             @OldQuantity, @NewQuantity,
             @OldMaterialUnit, @NewMaterialUnit,
             @OldMaterialDescription, @NewMaterialDescription,
             @OldConsumptionDeduction, @NewConsumptionDeduction,
             @OldMaterialGroupNumber, @NewMaterialGroupNumber)";

            MySqlParameter[] parameters = {
        new MySqlParameter("@MaterialId", history.MaterialId),
        new MySqlParameter("@ChangeType", history.ChangeType),
        new MySqlParameter("@ChangeTime", history.ChangeTime),
        new MySqlParameter("@OldMaterialType", history.OldMaterialType ?? (object)DBNull.Value),
        new MySqlParameter("@NewMaterialType", history.NewMaterialType ?? (object)DBNull.Value),
        new MySqlParameter("@OldDetailedMaterialType", history.OldDetailedMaterialType ?? (object)DBNull.Value),
        new MySqlParameter("@NewDetailedMaterialType", history.NewDetailedMaterialType ?? (object)DBNull.Value),
        new MySqlParameter("@OldQuantity", history.OldQuantity ?? (object)DBNull.Value),
        new MySqlParameter("@NewQuantity", history.NewQuantity ?? (object)DBNull.Value),
        new MySqlParameter("@OldMaterialUnit", history.OldMaterialUnit ?? (object)DBNull.Value),
        new MySqlParameter("@NewMaterialUnit", history.NewMaterialUnit ?? (object)DBNull.Value),
        new MySqlParameter("@OldMaterialDescription", history.OldMaterialDescription ?? (object)DBNull.Value),
        new MySqlParameter("@NewMaterialDescription", history.NewMaterialDescription ?? (object)DBNull.Value),
        new MySqlParameter("@OldConsumptionDeduction", history.OldConsumptionDeduction ?? (object)DBNull.Value),
        new MySqlParameter("@NewConsumptionDeduction", history.NewConsumptionDeduction ?? (object)DBNull.Value),
        new MySqlParameter("@OldMaterialGroupNumber", history.OldMaterialGroupNumber ?? (object)DBNull.Value),
        new MySqlParameter("@NewMaterialGroupNumber", history.NewMaterialGroupNumber ?? (object)DBNull.Value)
    };

            return DBHelper.ExecuteNonQuery(sql, parameters);
        }
        // 新增：获取物料历史记录
        public List<Model.Material.History> GetMaterialHistory(int materialId, string changeType = null)
        {
            string sql = "SELECT * FROM materialHistory WHERE MaterialId = @MaterialId";
            List<MySqlParameter> parameters = new List<MySqlParameter>
            {
                new MySqlParameter("@MaterialId", materialId)
            };

            if (!string.IsNullOrEmpty(changeType))
            {
                sql += " AND ChangeType = @ChangeType";
                parameters.Add(new MySqlParameter("@ChangeType", changeType));
            }

            sql += " ORDER BY ChangeTime DESC";

            return DBHelper.ExecuteQuery(sql, parameters.ToArray(), MapMaterialHistory);
        }
        // 新增：获取所有删除记录
        public List<Model.Material.History> GetAllDeletedMaterials()
        {
            string sql = "SELECT * FROM materialHistory WHERE ChangeType = 'DELETE' ORDER BY ChangeTime DESC";
            return DBHelper.ExecuteQuery(sql, null, MapMaterialHistory);
        }

        // 映射历史记录
        private Model.Material.History MapMaterialHistory(MySqlDataReader reader)
        {
            return new Model.Material.History
            {
                HistoryId = (int)reader["HistoryId"],
                MaterialId = (int)reader["MaterialId"],
                ChangeType = reader["ChangeType"].ToString(),
                ChangeTime = (DateTime)reader["ChangeTime"],
                // 修复：统一使用"Old"和"New"前缀
                OldMaterialType = reader["OldMaterialType"] is DBNull ? null : reader["OldMaterialType"].ToString(),
                NewMaterialType = reader["NewMaterialType"] is DBNull ? null : reader["NewMaterialType"].ToString(),
                OldDetailedMaterialType = reader["OldDetailedMaterialType"] is DBNull ? null : reader["OldDetailedMaterialType"].ToString(),
                NewDetailedMaterialType = reader["NewDetailedMaterialType"] is DBNull ? null : reader["NewDetailedMaterialType"].ToString(),
                OldQuantity = reader["OldQuantity"] is DBNull ? (int?)null : Convert.ToInt32(reader["OldQuantity"]),
                NewQuantity = reader["NewQuantity"] is DBNull ? (int?)null : Convert.ToInt32(reader["NewQuantity"]),
                OldMaterialUnit = reader["OldMaterialUnit"] is DBNull ? null : reader["OldMaterialUnit"].ToString(),
                NewMaterialUnit = reader["NewMaterialUnit"] is DBNull ? null : reader["NewMaterialUnit"].ToString(),
                OldMaterialDescription = reader["OldMaterialDescription"] is DBNull ? null : reader["OldMaterialDescription"].ToString(),
                NewMaterialDescription = reader["NewMaterialDescription"] is DBNull ? null : reader["NewMaterialDescription"].ToString(),
                OldConsumptionDeduction = reader["OldConsumptionDeduction"] is DBNull ? null : reader["OldConsumptionDeduction"].ToString(),
                NewConsumptionDeduction = reader["NewConsumptionDeduction"] is DBNull ? null : reader["NewConsumptionDeduction"].ToString(),
                OldMaterialGroupNumber = reader["OldMaterialGroupNumber"] is DBNull ? null : reader["OldMaterialGroupNumber"].ToString(),
                NewMaterialGroupNumber = reader["NewMaterialGroupNumber"] is DBNull ? null : reader["NewMaterialGroupNumber"].ToString()
            };
        }
        private int ExecuteNonQueryInTransaction(string sql, MySqlParameter[] parameters,
                                        MySqlConnection conn, MySqlTransaction transaction)
        {
            using (MySqlCommand cmd = new MySqlCommand(sql, conn, transaction))
            {
                if (parameters != null)
                    cmd.Parameters.AddRange(parameters);
                return cmd.ExecuteNonQuery();
            }
        }
    }
}